package cetb.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import cetb.domain.Offer;
import cetb.domain.Product;
import cetb.utils.UIDGenerator;

public class JDBCProductDAO implements IProductDAO {

	IOfferDAO odao;

	/*
	 * Con este metodo dada una tienda nos devuelve la lista de productos de esa tienda.
	 */
	public List<Product> selectAllProducts(Connection conn, String shopOID) {
		List<Product> products = new LinkedList<Product>();
		PreparedStatement stmt = null;
		ResultSet result = null;

		try {
			String sql = "SELECT productID, description, price, stock, category FROM product WHERE product.shopOID= ? ;";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, shopOID);
			result = stmt.executeQuery();

			while (result.next()) {
				Product p = new Product();
				p.setProductID(result.getString("productID"));
				p.setCategory(result.getString("category"));
				p.setDescription(result.getString("description"));
				p.setPrice(result.getFloat("price"));
				p.setStock(result.getFloat("stock"));
				products.add(p);
			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}
		return products;
	}

	public List<Product> selectShopProducts(Connection conn, String shopOID) {
		List<Product> products = new LinkedList<Product>();
		String sql = "SELECT * FROM product WHERE shopOID = ?";
		PreparedStatement stmt = null;
		ResultSet result = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, shopOID);
			result = stmt.executeQuery();

			while (result.next()) {

				Product p = odao.selectOffer(conn, result.getString("OID"));
				if (p != null) {
					p = new Offer();
				} else {
					p = new Product();
				}

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}
		return products;
	}
	/*
	 * Con ese metodo dada una clave de un producto, nos devolvera el producto en si.
	 */
	public Product selectProduct(Connection conn, String poid) {
		PreparedStatement stmt = null;
		ResultSet result = null;
		Product p=null;
		try {
			String sql = "SELECT productID, description, price, category, stock FROM product WHERE product.OID= ? ;";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, poid);
			result = stmt.executeQuery();
			
			result.next();
			p=new Product();
			p.setProductID(result.getString("productID"));
			p.setCategory(result.getString("category"));
			p.setDescription(result.getString("description"));
			p.setPrice(result.getFloat("price"));
			p.setStock(result.getFloat("stock"));
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}
		return p;
	}

	/*
	 * Con este metodo dada la clave de un producto se borra de la base de datos
	 * @see cetb.data.IProductDAO#delete(java.sql.Connection, java.lang.String)
	 */
	public void delete(Connection conn, String poid) {
		PreparedStatement stmt=null;
		
		try {
			String sql = "DELETE FROM product WHERE product.OID= ? ;";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, poid);
			stmt.executeUpdate();
						
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}

	}
	
	/*
	 * Con este metodo dado un producto y la clave de la tienda donde pertenece, lo insertamos en la bd
	 * @see cetb.data.IProductDAO#insert(java.sql.Connection, cetb.domain.Product, java.lang.String)
	 */
	public void insert(Connection conn, Product p, String soid) {
		PreparedStatement stmt=null;
		String oid = UIDGenerator.getInstance().getKey();
		
		try {
			String sql = "INSERT INTO product (OID, productID, description, price, stock, shopOID, category) VALUES (?, ? ,?, ?, ?, ?, ?);";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, oid);
			stmt.setString(2, p.getProductID());
			stmt.setString(3, p.getDescription());
			stmt.setFloat(4, p.getPrice());
			stmt.setFloat(5, p.getStock());
			stmt.setString(6, soid);
			stmt.setString(7, p.getCategory());
			stmt.executeUpdate();
						
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}

	}

	/*
	 * Con este metodo actualizamos todos los campos (actualizables) de un producto dado por su clave.
	 * @see cetb.data.IProductDAO#update(java.sql.Connection, cetb.domain.Product, java.lang.String)
	 */
	public void update(Connection conn, Product p, String poid) {
		PreparedStatement stmt=null;
			
		try {
			String sql = "UPDATE product SET product.description=? , product.price= ?, product.stock= ?, product.category= ? WHERE product.OID= ?;";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, p.getDescription());
			stmt.setFloat(2, p.getPrice());
			stmt.setFloat(3, p.getStock());
			stmt.setString(4, p.getCategory());
			stmt.setString(5, poid);
			stmt.executeUpdate();
						
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}

	}
	public String selectProductOID(Connection conn, String productID){
		PreparedStatement stmt = null;
		ResultSet result = null;
		String poid=null;
		try {
			String sql = "SELECT OID FROM product WHERE product.productID= ? ;";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, productID);
			result = stmt.executeQuery();
			
			result.next();
			poid=result.getString("OID");
			
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (result != null) {
					result.close();
				}

				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {

			}
		}
		return poid;
	}

}
